﻿using System;
using System.Collections.Generic;
using System.Text;
using LibraryONTrack.dto;
using System.Data.SQLite;

namespace LibraryONTrack.dao
{
	public class StudentDAO : PersonDAO
	{
		///
		public List<Student> GetAllStudents()
		{
			string query = @"SELECT
											   student_teacher_id, name, teacher_id
                       FROM roster
											 WHERE teacher_id is not null;";
			SqlDao dao = SqlDao;
			return dao.GetList<Student>(dao.GetSQLiteCommand(query));
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="teacherId"></param>
		/// <returns></returns>
		public List<Student> GetAllStudents(long teacherId)
		{
			string query = @"SELECT
  										   student_teacher_id, name, teacher_id
                       FROM roster
											 WHERE teacher_id = @teacher_id;";
			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@teacher_id", teacherId));
			return dao.GetList<Student>(command);
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="studentId"></param>
		/// <returns></returns>
		public Student GetStudent(long id)
		{
			string query = @"SELECT
  										   student_teacher_id, name, teacher_id
                       FROM roster
											 WHERE student_teacher_id = @id and
														 teacher_id is not null;";
			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@id", id));
			return dao.GetSingle<Student>(command);
		}

		/// <summary>
		/// 
		/// </summary>
		/// <param name="studentName"></param>
		/// <returns></returns>
		public Student GetStudent(string studentName)
		{
			string query = @"SELECT
  										   student_teacher_id, name, teacher_id
                       FROM roster
											 WHERE lower(name) = lower(@student_name) and
														 teacher_id is not null;";
			SqlDao dao = SqlDao;
			SQLiteCommand command = dao.GetSQLiteCommand(query);
			command.Parameters.Add(dao.CreateParameter("@student_name", studentName, 64));
			return dao.GetSingle<Student>(command);
		}

		public Student Insert(string name, long teacherId)
		{
			Student student = new Student();
			student.Name = name;
			student.TeacherId = teacherId;
			this.Insert(student);
			return student;
		}

		public void Insert(Student t)
		{
			SqlDao dao = SqlDao;
			string sql = @"insert into [roster]
										   (name, teacher_id)
										 values
										   (@name, @teacher_id);
										 select last_insert_rowid();";
			SQLiteCommand command = dao.GetSQLiteCommand(sql);
			command.Parameters.Add(dao.CreateParameter("@name", t.Name, 64));
			command.Parameters.Add(dao.CreateParameter("@teacher_id", t.TeacherId));
			t.Id = (long)dao.ExecuteScalar(command);
		}




	}
}
